package com.envocabulary.Model;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseController extends SQLiteOpenHelper {

	private SQLiteDatabase myDataBase;
	private final Context myContext;
	private static final String DATABASE_NAME = "lesson.sqlite";
	public final static String DATABASE_PATH = "/data/data/com.envocabulary.widget_list/databases/";
	public static final int DATABASE_VERSION = 1;

	// Constructor
	public DatabaseController(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.myContext = context;
	}

	public void createDatabase() throws IOException {

		boolean dbExist = checkDataBase();

		if (dbExist) {
			Log.v("DB Exists", "db exists");
			// By calling this method here onUpgrade will be called on a
			// writeable database, but only if the version number has been
			// bumped
			// onUpgrade(myDataBase, DATABASE_VERSION_old, DATABASE_VERSION);
		} else {
			this.getReadableDatabase();
			try {
				this.close();
				copyDataBase();
			} catch (IOException e) {
				throw new Error("Error copying database");
			}
		}

	}

	// Check database already exist or not
	private boolean checkDataBase() {
		boolean checkDB = false;
		try {
			String myPath = DATABASE_PATH + DATABASE_NAME;
			File dbfile = new File(myPath);
			checkDB = dbfile.exists();
		} catch (SQLiteException e) {
		}
		return checkDB;
	}

	private void copyDataBase() throws IOException {
		String outFileName = DATABASE_PATH + DATABASE_NAME;
		OutputStream myOutput = new FileOutputStream(outFileName);
		InputStream myInput = myContext.getAssets().open(DATABASE_NAME);

		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}
		myInput.close();
		myOutput.flush();
		myOutput.close();
	}

	// delete database
	public void db_delete() {
		File file = new File(DATABASE_PATH + DATABASE_NAME);
		if (file.exists()) {
			file.delete();
			System.out.println("delete database file.");
		}
	}

	// Open database
	public void openDatabase() throws SQLException {
		String myPath = DATABASE_PATH + DATABASE_NAME;
		myDataBase = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READWRITE);
	}

	public synchronized void closeDataBase() throws SQLException {
		if (myDataBase != null)
			myDataBase.close();
		super.close();
	}

	public void onCreate(SQLiteDatabase db) {
	}

	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (newVersion > oldVersion) {
			Log.v("Database Upgrade", "Database version higher than old.");
			db_delete();
		}
	}

	public Word getWordFromDB(int id) {
		String query = "SELECT * FROM word where _id=" + id;
		Cursor cursor = myDataBase.rawQuery(query, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		Word mWord = new Word(cursor);
		return mWord;
	}

	public List<Word> getRandomWords(int limit) {
		String query = String.format(
				"SELECT * FROM word ORDER BY RANDOM() LIMIT %s",
				String.valueOf(limit));
		List<Word> words = new ArrayList<Word>();
		Cursor cursor = myDataBase.rawQuery(query, null);
		Word word;
		int id;
		String w, phonetic, meanings, summary;
		if (cursor != null) {
			while (cursor.moveToNext()) {
				id = cursor.getInt(cursor.getColumnIndex("_id"));
				w = cursor.getString(cursor.getColumnIndex("sword"));
				phonetic = cursor.getString(cursor.getColumnIndex("sphonetic"));
				meanings = cursor.getString(cursor.getColumnIndex("smeanings"));
				summary = cursor.getString(cursor.getColumnIndex("ssummary"));
				word = new Word(id, w, phonetic, meanings, summary);
				words.add(word);
			}
		}

		return words;
	}

	public Verbs getVerbsFromDB(int id) {
		String query = "SELECT * FROM verbs where _id=" + id;
		Cursor cursor = myDataBase.rawQuery(query, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		Verbs mVerb = new Verbs(cursor);
		return mVerb;
	}

	public List<Verbs> getRandomVerbs(int limit) {
		String query = String.format(
				"SELECT * FROM irregular_verbs ORDER BY RANDOM() LIMIT %s",
				String.valueOf(limit));
		List<Verbs> words = new ArrayList<Verbs>();
		Cursor cursor = myDataBase.rawQuery(query, null);
		if (cursor != null) {
			while (cursor.moveToNext()) {
				Verbs mVerbs = new Verbs(cursor);
				words.add(mVerbs);
			}
		}

		return words;
	}

	public Phrases getPhrasesFromDB(int id) {
		String query = "SELECT * FROM phrases where _id=" + id;
		Cursor cursor = myDataBase.rawQuery(query, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		Phrases mPh = new Phrases(cursor);
		return mPh;
	}

	public List<Phrases> getRandomPharses(int limit) {
		String query = String.format(
				"SELECT * FROM phrases ORDER BY RANDOM() LIMIT %s",
				String.valueOf(limit));
		List<Phrases> words = new ArrayList<Phrases>();
		Cursor cursor = myDataBase.rawQuery(query, null);
		if (cursor != null) {
			while (cursor.moveToNext()) {
				Phrases mPH = new Phrases(cursor);
				words.add(mPH);
			}
		}

		return words;
	}

}
